<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>innodb_transactions: common_schema documentation</title>
	<meta name="description" content="innodb_transactions: common_schema" />
	<meta name="keywords" content="innodb_transactions: common_schema" />
	<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>

<body>
	<div id="main">
		<div id="header">
			<h1>common_schema</h1> <strong>2.2</strong> documentation
			<div class="subtitle">DBA's framework for MySQL</div>
		</div>
		<div id="contentwrapper">
			<div id="content">
				<h2><a href="innodb_transactions.html">innodb_transactions</a></h2>	
<h3>NAME</h3>
innodb_transactions: Listing of open (InnoDB Plugin) transactions
<h3>TYPE</h3>
View

<h3>DESCRIPTION</h3>
<p></p>

<p>
	<i>innodb_transactions</i> is a simplification of INFORMATION_SCHEMA.INNODB_TRX. 
</p>

<p>
	<strong>CHANGE:</strong> up to common_schema <strong>1.1</strong>, this view would only show
	transactions that are executing statements. It now lists all open transactions, and offers the
	<strong>trx_idle_seconds</strong> columns to help finding sleeping open transactions.
</p>

<p>
	The connection calling upon this view is never listed.
</p>

<h3>STRUCTURE</h3>

<blockquote><pre>
mysql&gt; DESC common_schema.innodb_transactions;
+-----------------------+---------------------+------+-----+---------------------+-------+
| Field                 | Type                | Null | Key | Default             | Extra |
+-----------------------+---------------------+------+-----+---------------------+-------+
| trx_id                | varchar(18)         | NO   |     |                     |       |
| trx_state             | varchar(13)         | NO   |     |                     |       |
| trx_started           | datetime            | NO   |     | 0000-00-00 00:00:00 |       |
| trx_requested_lock_id | varchar(81)         | YES  |     | NULL                |       |
| trx_wait_started      | datetime            | YES  |     | NULL                |       |
| trx_weight            | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_mysql_thread_id   | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_query             | varchar(1024)       | YES  |     | NULL                |       |
| INFO                  | longtext            | YES  |     | NULL                |       |
| trx_runtime_seconds   | bigint(21)          | YES  |     | NULL                |       |
| trx_wait_seconds      | bigint(21)          | YES  |     | NULL                |       |
| trx_idle_seconds      | bigint(11)          | YES  |     | NULL                |       |
| sql_kill_query        | varbinary(31)       | NO   |     |                     |       |
| sql_kill_connection   | varbinary(25)       | NO   |     |                     |       |
+-----------------------+---------------------+------+-----+---------------------+-------+
</pre></blockquote>

<h3>SYNOPSIS</h3>

<p>Structure of this view derives from that of <a href="http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-information-schema-innodb_trx.html">INFORMATION_SCHEMA.INNODB_TRX</a> table.</p>

<p>Additional columns are:
	<ul>
		<li><strong>INFO</strong>: Query being executed right now by this transaction, 
			as seen on <strong>PROCESSLIST</strong>.</li>
		<li><strong>trx_runtime_seconds</strong>: number of seconds elapsed since beginning of this transaction.</li>
		<li>
			<strong>trx_wait_seconds</strong>: number of seconds this transaction is waiting on lock, 
			or <strong>NULL</strong> if not currently waiting.
		</li>
		<li><strong>trx_idle_seconds</strong>: number of seconds this transaction is idle. <strong>0</strong> if not idle.</li>
		<li>
			<strong>sql_kill_query</strong>: a <strong>KILL QUERY</strong> statement for current thread.
			<br/>Use with <a href="eval.html">eval()</a> to apply statement.	
		</li>
		<li>
			<strong>sql_kill_connection</strong>: a <strong>KILL</strong> statement for current thread.
			<br/>Use with <a href="eval.html">eval()</a> to apply statement.	
		</li>
	</ul>
</p>

<h3>EXAMPLES</h3>

<p>Show all active transactions:
<blockquote><pre>mysql&gt; SELECT * FROM common_schema.innodb_transactions;
+-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+-----------------------------------------------------------------------------------+---------------------+------------------+------------------+-------------------+---------------------+
| trx_id    | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | INFO                                                                              | trx_runtime_seconds | trx_wait_seconds | trx_idle_seconds | sql_kill_query    | sql_kill_connection |
+-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+-----------------------------------------------------------------------------------+---------------------+------------------+------------------+-------------------+---------------------+
| 9AA6213B4 | RUNNING   | 2012-09-27 15:46:36 | NULL                  | NULL             |         13 |              858223 | NULL      | DELETE FROM tbl_lock WHERE id = 'planner' AND expiryTime < '2012-09-27 15:46:36'  |                   0 |             NULL |                0 | KILL QUERY 858223 | KILL 858223         |
| 9AA6213B2 | RUNNING   | 2012-09-27 15:46:36 | NULL                  | NULL             |          3 |              858216 | NULL      | NULL                                                                              |                   0 |             NULL |                0 | KILL QUERY 858216 | KILL 858216         |
| 9AA6213B2 | RUNNING   | 2012-09-27 15:46:36 | NULL                  | NULL             |          3 |              858219 | NULL      | UPDATE tbl_scount SET count = count + 1 WHERE element='php'                       |                   0 |             NULL |                0 | KILL QUERY 858219 | KILL 858219         |
+-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+-----------------------------------------------------------------------------------+---------------------+------------------+------------------+-------------------+---------------------+
</pre></blockquote>
In the above no transaction is waiting and no transaction is idle.
</p>


<p>Kill transactions idle for <strong>30</strong> seconds or more:
<blockquote><pre>mysql&gt; CALL eval("SELECT sql_kill_query FROM common_schema.innodb_transactions WHERE trx_idle_seconds >= 30");
</pre></blockquote>
In the above no transaction is waiting and no transaction is idle.
</p>


<h3>ENVIRONMENT</h3>
MySQL 5.1 with InnoDB Plugin installed (with InnoDB INFORMATION_SCHEMA plugins enabled), or MySQL >= 5.5

<h3>SEE ALSO</h3>
<a href="innodb_locked_transactions.html">innodb_locked_transactions</a>,
<a href="innodb_simple_locks.html">innodb_simple_locks</a>,
<a href="innodb_transactions_summary.html">innodb_transactions_summary</a>

<h3>AUTHOR</h3>
Shlomi Noach
				<br/>
			</div>
			<div id="sidebarwrapper">
				<div id="search">
					Search online documentation
					<form id="search_form" name="search_form" method="GET" 
						action="http://www.google.com/search" 
						onsubmit="document.forms['search_form']['q'].value = 'site:http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/ '+document.forms['search_form']['search_term'].value;">
						<input type="text" name="search_term" value=""/>
						<input type="hidden" name="q" value=""/>
						<input type="submit" value="go"/>						
					</form>
				</div>
				<div id="menu">
					<ul>
						<li><a title="Introduction" href="introduction.html">Introduction</a></li>
						<li><a title="Documentation" href="documentation.html">Documentation</a></li>
						<li><a title="Download" href="download.html">Download</a></li>
						<li><a title="Install" href="install.html">Install</a></li>
						<li><a title="Risks" href="risks.html">Risks</a></li>
					</ul>						
					<h3>QUERY SCRIPT</h3>
					<ul>
						<li><a title="QueryScript" href="query_script.html">QueryScript</a></li>
						<li><a title="Execution" href="query_script_execution.html">Execution</a></li>
						<li><a title="Flow control" href="query_script_flow_control.html">Flow control</a></li>
						<li><a title="Statements" href="query_script_statements.html">Statements</a></li>
						<li><a title="Expressions" href="query_script_expressions.html">Expressions</a></li>
						<li><a title="Variables" href="query_script_variables.html">Variables</a></li>
					</ul>						
					<h3>DEBUG</h3>
					<ul>
						<li><a title="rdebug" href="rdebug.html">rdebug</a></li>
						<li><a title="rdebug API" href="rdebug_api.html">rdebug API</a></li>
						<li><a title="rdebug workflow" href="rdebug_workflow.html">Workflow</a></li>
					</ul>						
					<h3>ROUTINES</h3>
					<ul>
						<li><a title="Execution &amp; flow control" href="execution_routines.html">Execution & flow control</a></li>
						<li><a title="General" href="general_routines.html">General</a></li>
						<li><a title="Process" href="process_routines.html">Process</a></li>
						<li><a title="Query analysis" href="query_analysis_routines.html">Query analysis</a></li>
						<li><a title="Schema analysis" href="schema_analysis_routines.html">Schema analysis</a></li>
						<li><a title="Security" href="security_routines.html">Security</a></li>
						<li><a title="Text" href="text_routines.html">Text</a></li>
						<li><a title="Time &amp; date" href="temporal_routines.html">Time & date</a></li>
						<li><a title="Charting" href="charting_routines.html">Charting</a></li>
					</ul>
					<h3>VIEWS</h3>
					<ul>
						<li><a title="Schema analysis" href="schema_analysis_views.html">Schema analysis</a></li>
						<li><a title="Data dimension" href="data_dimension_views.html">Data dimension</a></li>
						<li><a title="Process" href="process_views.html">Process</a></li>
						<li><a title="Security" href="security_views.html">Security</a></li>
						<li><a title="Monitoring" href="monitoring_views.html">Monitoring</a></li>
						<li><a title="InnoDB Plugin" href="innodb_plugin_views.html">InnoDB Plugin</a></li>
						<li><a title="Percona server" href="percona_server_views.html">Percona Server</a></li>
						<li><a title="TokuDB" href="tokudb_views.html">TokuDB</a></li>
					</ul>						
					<h3>DATA</h3>
					<ul>
						<li><a title="tables" href="tables.html">Tables</a></li>
						<li><a title="variables" href="variables.html">Variables</a></li>
					</ul>						
					<h3>META</h3>
					<ul>
						<li><a title="Help" href="help.html">help</a></li>
						<li><a title="Metadata" href="metadata.html">metadata</a></li>
						<li><a title="status" href="status.html">status</a></li>
					</ul>						
				</div>
			</div>	
			<div class="clear">&nbsp;</div>
			
			<div id="footnote" align="center">
				<a href="">common_schema</a> documentation
			</div>
		</div>
	</div>
</body>
</html>
